﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

using System.Security.Cryptography;
using System.Diagnostics;
using QLDHS;


namespace DAO
{
    public class OleDbConnectiondata
    {
        public static OleDbConnection connect()
        {
            String sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\QLNS_TRANGNT\\QLDHS.accdb;Persist Security Info=False;";
            OleDbConnection cn = new OleDbConnection(sql);
            return cn;
        }
    }
    public class mahoa
    {

    }
    public class Login_DAO
    {
        public static string ToMD5(string str)
        {

            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();

            byte[] bHash = md5.ComputeHash(Encoding.UTF8.GetBytes(str));

            StringBuilder sbHash = new StringBuilder();

            foreach (byte b in bHash)
            {

                sbHash.Append(String.Format("{0:x2}", b));

            }
            return sbHash.ToString();

        }
        public static int LoadUser(string user, string pass)
        {
            //try
            //{
            int checkLogin = 0;

            OleDbConnection cn = Program.GetConnection();
            OleDbCommand cmd = new OleDbCommand("select * from _user ", cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = user;
            //cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = pass;//ToMD5(pass);
            cn.Open();
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (user.Equals(dr[1].ToString())&& pass.Equals(dr[2].ToString()))
                {

                    checkLogin = 1;
                }
                else
                {
                    checkLogin = 0;
                }

            }
            return checkLogin;
            cn.Close();

            //}
            //catch (Exception ex)
            //{
            //    Trace.Write("" + ex) ;
            //}
            //finally
            //{

            //}
        }
        public static string Loadpass(string user)
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("pcd_sel_pas", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = user;
            cn.Open();
            OleDbDataReader dr = cmd.ExecuteReader();
            dr.Read();
            return dr[0].ToString();
        }
        public static void save(string a, string b)
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("insert into _user values('" + a + "','" + b + "'", cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@i", SqlDbType.NVarChar,50).Value = b;
            //cmd.Parameters.Add("@e", SqlDbType.NVarChar, 50).Value = a;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();

        }


    }
    public class Per_DAO
    {
        public static DataTable get_per()
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("pcd_getper", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cn.Close();
            return dtb;
        }
        public static DataTable getquyen()
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("getquyen", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cn.Close();
            return dtb;

        }
        public static DataTable tk(string a)
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("pcd_per_tk", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@i", SqlDbType.NVarChar).Value = a;
            cn.Open();
            cmd.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cn.Close();
            return dtb;

        }
        public static DataTable profile(string a)
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("showDetail", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ma_nv", SqlDbType.Int).Value = Convert.ToInt32(a);
            cn.Open();
            cmd.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cn.Close();
            return dtb;

        }
        public static void save(int a, string b)
        {
            OleDbConnection cn = OleDbConnectiondata.connect();
            OleDbCommand cmd = new OleDbCommand("pcd_per_save", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.Add("@i", SqlDbType.Int).Value = a;
            //cmd.Parameters.Add("@e", SqlDbType.NVarChar,50).Value = b;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();

        }

    }

}
